﻿using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ImportBLSData.ap
{
    public class Area
    {
        public static void ProcessAPAreaFile(FileInfo fi)
        {
            StreamReader fs = fi.OpenText();
            string colNames = fs.ReadLine();

            Server svr = new Server();
            svr.ConnectionContext.LoginSecure = true;
            Database dbGNIS = svr.Databases["GNISCensusParcelMLS"];
            if (dbGNIS.Tables[fi.Name] == null)
            {
                Table apArea = new Table(dbGNIS, fi.Name);
                Column colAreaCode = new Column(apArea, "AreaCode", DataType.NVarChar(4), false) { Nullable = true };
                Column colAreaName = new Column(apArea, "AreaName", DataType.NVarChar(80), false) { Nullable = true };
                apArea.Columns.Add(colAreaCode);
                apArea.Columns.Add(colAreaName);
                apArea.Create();
            }

            string line = string.Empty;
            StringCollection sqlCommands = new StringCollection();
            while ((line = fs.ReadLine()) != null)
            {
                string[] values = line.Split(new[] { "\t" }, StringSplitOptions.None);
                sqlCommands.Add("if not exists (select 1 from [" + dbGNIS.Name + "].[dbo].[" + fi.Name + "] where AreaCode = '" + values[0] + "') begin insert into [" + dbGNIS.Name + "].[dbo].[" + fi.Name + "] (AreaCode, AreaName) values ('" + values[0] + "', '" + values[1] + "') end");
            }
            using (SqlConnection cn = new SqlConnection(svr.ConnectionContext.ConnectionString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(string.Empty, cn))
                {
                    foreach (string s in sqlCommands)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
